package a_aa_amainpackage.qiye_test.address;

/**
 * @ Auther: o2o-rd-0008
 * @ Date:   2021/2/7 16:47
 * @ Param:  ${PARAM}
 * @ Description:
 */
public class TestSql {
    public static void main(String[] args) {
        String month_interval = "2021-01";
        String schema = "swb_schema";
        String sql = "select \n" +
                "\"MONTH_INTERVAL\",\n" +
                "\"INDICATOR_CLASS\",\n" +
                "\"INDICATOR\",\n" +
                "\"VALUE\",\n" +
                "\"VALUE_GROWTH_RATE\",\n" +
                "\"VALUE_TOTAL\",\n" +
                "\"VALUE_GROWTH_RATE_TOTAL\",\n" +
                "shopCount\n" +
                " from (\n" +
                "select monthS_OR_CUR \"MONTH_INTERVAL\",\"INDICATOR_CLASS\",\"INDICATOR\",\n" +
                "(\"VALUE_OR_CUR\"/100000000) \"VALUE\",\n" +
                "((\"VALUE\" - \"VALUE18\")/\"VALUE18\")*100 \"VALUE_GROWTH_RATE\",\n" +
                "(\"VALUE_OR_CUR_TOTAL\"/100000000) \"VALUE_TOTAL\",\n" +
                "((\"VALUE_TOTAL\" - \"VALUE_TOTAL18\")/\"VALUE_TOTAL18\")*100 \"VALUE_GROWTH_RATE_TOTAL\"\n" +
                " from \n" +
                "(\n" +
                "select indicatory_or_cur.*,indicatory19.\"VALUE\",indicatory18.\"VALUE\" AS \"VALUE18\",\n" +
                "\n" +
                "sum(indicatory_or_cur.\"VALUE_OR_CUR\") over( partition by indicatory_or_cur.\"INDICATOR\" ORDER BY months19 ) \"VALUE_OR_CUR_TOTAL\",\n" +
                "\n" +
                "sum(indicatory19.\"VALUE\") over( partition by indicatory19.\"INDICATOR\" ORDER BY months19 ) \"VALUE_TOTAL\",\n" +
                "sum(indicatory18.\"VALUE\") over( partition by indicatory18.\"INDICATOR\" ORDER BY months18 ) \"VALUE_TOTAL18\"\n" +
                " from \n" +
                "(\n" +
                "\t\t-- 每个月总数\n" +
                "\t\tselect to_char(to_timestamp(timestamp),'YYYY-MM') monthS_OR_CUR,\n" +
                "\t\t'按平台分' as \"INDICATOR_CLASS\",\n" +
                "\t\tswbplatname as \"INDICATOR\",\n" +
                "\t\tsum(salesAmount) \"VALUE_OR_CUR\" from \n" +
                "\t\t(\n" +
                "\t\t       select s1.*,swbplatname from \""+schema+"\".\"o2o_screen_ovresea_app_2021\" s1 left join \n" +
                "\t\t       (select p1.platformId,p2.swbplatname from dbadmin.\"O2O_stand_platform_IDL\" p1 left join \"swb_schema\".\"O2O_stand_platform_swb\" p2\n" +
                "\t\t        on p1.platformname = p2.name) s2 on s1.platformId = s2.platformId\n" +
                "\t\t       where firstCategoryId not in ('10026','10027','10028','10029','10030','10031','10032') \n" +
                "\n" +
                "\t\t ) t1 \n" +
                "\t\t group by timestamp,swbplatname\n" +
                "\t\n" +
                ")  indicatory_or_cur left join \n" +
                "\n" +
                "-- 同比计算基础\n" +
                "(\n" +
                "\t\t-- 每个月总数\n" +
                "\t\tselect to_char(to_timestamp(timestamp),'YYYY-MM') monthS19,\n" +
                "\t\t'按平台分' as \"INDICATOR_CLASS\",\n" +
                "\t\tswbplatname as \"INDICATOR\",\n" +
                "\t\tsum(salesAmount) \"VALUE\" from \n" +
                "\t\t(\n" +
                "\t\t       select s1.*,swbplatname from \""+schema+"\".\"o2o_screen_ovresea_app_2021\" s1 left join \n" +
                "\t\t       (select p1.platformId,p2.swbplatname from dbadmin.\"O2O_stand_platform_IDL\" p1 left join \"swb_schema\".\"O2O_stand_platform_swb\" p2\n" +
                "\t\t        on p1.platformname = p2.name) s2 on s1.platformId = s2.platformId\n" +
                "\t\t       where firstCategoryId not in ('10026','10027','10028','10029','10030','10031','10032') \n" +
                "\n" +
                "\t\t ) t1 \n" +
                "\t\t group by timestamp,swbplatname\n" +
                "\t\n" +
                ")  indicatory19  \n" +
                "   on \n" +
                "\t--indicatory19.monthS = indicatory18.monthS\n" +
                "\tsubstr(monthS_OR_CUR,5,6) = substr(monthS19,5,6)\n" +
                "\tand indicatory_or_cur.\"INDICATOR\" = indicatory19.\"INDICATOR\" \n" +
                "left join \n" +
                "(\n" +
                "\t\t-- 每个月总数\n" +
                "\t\tselect to_char(to_timestamp(timestamp),'YYYY-MM') monthS18,\n" +
                "\t\t'按平台分' as \"INDICATOR_CLASS\",\n" +
                "\t\tswbplatname as \"INDICATOR\",\n" +
                "\t\tsum(salesAmount) \"VALUE\" from \n" +
                "\t\t(\n" +
                "\t\t       select s1.*,swbplatname from \""+schema+"\".\"o2o_screen_ovresea_app_2020\" s1 left join \n" +
                "\t\t       (select p1.platformId,p2.swbplatname from dbadmin.\"O2O_stand_platform_IDL\" p1 left join \"swb_schema\".\"O2O_stand_platform_swb\" p2\n" +
                "\t\t        on p1.platformname = p2.name) s2 on s1.platformId = s2.platformId\n" +
                "\t\t       where firstCategoryId not in ('10026','10027','10028','10029','10030','10031','10032') \n" +
                "\n" +
                "\t\t ) t1 \n" +
                "\t\t group by timestamp,swbplatname\n" +
                ") indicatory18 \n" +
                "\ton \n" +
                "\t--indicatory19.monthS = indicatory18.monthS\n" +
                "\tsubstr(monthS19,5,6) = substr(monthS18,5,6)\n" +
                "\tand indicatory19.\"INDICATOR\" = indicatory18.\"INDICATOR\"\n" +
                "\n" +
                ") result\n" +
                ") result2 \n" +
                "left join (\n" +
                "           select t1.monthShop,t2.swbplatname,count shopCount from \n" +
                "            (\n" +
                "               select to_char(to_timestamp(timestamp),'YYYY-MM') monthShop,platformId,count(distinct shopId) count from \n" +
                "               \"swb_schema\".\"o2o_screen_ovresea_app_2021\"   \n" +
                "               where firstCategoryId not in ('10026','10027','10028','10029','10030','10031','10032')\n" +
                "               group by platformId,timeStamp  \n" +
                "            )  t1 left join (select p1.platformId,p2.swbplatname from dbadmin.\"O2O_stand_platform_IDL\" p1 left join \"swb_schema\".\"O2O_stand_platform_swb\" p2\n" +
                "\t\t   on p1.platformname = p2.name) t2 on t1.platformId = t2.platformId\n" +
                ") shopCount on result2.\"MONTH_INTERVAL\" = shopCount.monthShop and result2.\"INDICATOR\" = shopCount.swbplatname\n" +
                " where \"MONTH_INTERVAL\" = '"+month_interval+"'\n" +
                ";\n"  ;


        System.out.println(sql);
    }
}
